import pandas as pd
import math

def combinations(n, k):
    if k < 0 or k > n:
        return 0
    return math.factorial(n) // (math.factorial(k) * math.factorial(n - k))

def sum_lower_triangle(matrix):
    total = 0
    for i in range(len(matrix)):
        for j in range(i + 1):
            total += matrix[i][j]
    return total

# 读取CSV文件
df = pd.read_csv('./电力训练数据/表结构.csv')

# 设置一个字典统计所有的表名
table_dict = {}
# 生成一个200*200的二维数组，用于存储表之间的连接关系
connection = [[0 for i in range(200)] for i in range(200)]

# 遍历csv中数据
i=0
total_foreign_keys_no = 0
for index, row in df.iterrows():
    # print(row['数据库表名'])
    if row['数据库表名'] not in set(table_dict.keys()):
        table_dict[row['数据库表名']]=i
        i+=1
    key_type = row['键类型']
    # print(key_type)
    if key_type == '外键':
        total_foreign_keys_no += 1
        to_table = row['键对应表']

        # 添加外键对应表
        if to_table not in set(table_dict.keys()):
            table_dict[to_table]=i
            i+=1
        
        from_table = row['数据库表名']
        connection[table_dict[from_table]][table_dict[to_table]] = 1
        connection[table_dict[to_table]][table_dict[from_table]] = 1
        # print(to_table, from_table)


table_no = len(table_dict)
print(table_no)
result = sum_lower_triangle(connection)
# print(connection)
db_connect_degree_dianli = total_foreign_keys_no/(combinations(table_no,2)+total_foreign_keys_no-result)
print(db_connect_degree_dianli)